<?php

require_once(SITE_ROOT . 'class/db/DB.class.php');
require_once(SITE_ROOT . 'class/db/DBFlag.class.php');
require_once(SITE_ROOT . 'class/db/DBAllianceMember.class.php');
require_once(SITE_ROOT . 'class/bean/Team.class.php');
require_once(SITE_ROOT . 'class/bean/Flag.class.php');
require_once(SITE_ROOT . 'class/bean/League.class.php');
require_once(SITE_ROOT . 'class/bean/Division.class.php');
require_once(SITE_ROOT . 'class/bean/Country.class.php');

class DBTeam {

	private $type = "";
	private $params = array();
	
	public function getCountForHoC($countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate) {
		$db = new DB();
		
		$connection = $db->connect();
		
		$select = "SELECT count(*) AS numrows";
		
		if ($stmt = $connection->prepare($this->createSelect($countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate, $select))) {
		
			if (count($this->params) > 0) {
				$bind_names = array();
				$bind_names[0] = $this->type;
			
				for ($i=0; $i<count($this->params); $i++) {
					$bind_names[$i+1] = $this->params[$i];
				}
				call_user_func_array(array($stmt, 'bind_param'), $bind_names);
			}			
			
			$stmt->execute();
			
			$stmt->bind_result($numrows);
					
			$count = 0;
			
			while ($stmt->fetch()) {
				$count = $numrows;
			}			
			$stmt->close();
			$db->disconnect();
			
			return $count;
		}
	}
	
	public function getTeamsForHoC($orderby, $countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate, $startindex, $endindex) {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		$select = "SELECT t.`team#`, t.`name`, t.`update`, t.`away_coolness`, t.`away_flags`, t.`home_coolness`, t.`home_flags`, t.`and_coolness`, t.`and_flags`, t.`or_coolness`, t.`or_flags`, t.`plus_coolness`, t.`plus_flags`, t.`user#`, t.`username`, t.`activationdate`, l.`league#`, l.`name`, l.`division#`, l.`country#`";
		$order = " ORDER BY t.`" . $orderby . "` DESC, t.`activationdate` DESC LIMIT " . $startindex . ", " . $endindex;
		
		if ($stmt = $connection->prepare($this->createSelect($countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate, $select, $order))) {
		
			if (count($this->params) > 0) {
				$bind_names = array();
				$bind_names[0] = $this->type;
			
				for ($i=0; $i<count($this->params); $i++) {
					$bind_names[$i+1] = $this->params[$i];
				}
				call_user_func_array(array($stmt, 'bind_param'), $bind_names);
			}			
			
			$stmt->execute();
			
			$stmt->bind_result($teamID, $name, $update, $away_coolness, $away_flags, $home_coolness, $home_flags, $and_coolness, $and_flags, $or_coolness, $or_flags, $plus_coolness, $plus_flags, $userID, $username, $activationdate, $leagueID, $leaguename, $divisionID, $countryID);
					
			$teams = new ArrayObject();
			
			while ($stmt->fetch()) {
				$country = new Country();
				$country->setCountryID($countryID);
				
				$division = new Division();
				$division->setDivisionID($divisionID);
				$division->setCountry($country);
				
				$league = new League();
				$league->setLeagueID($leagueID);
				$league->setName($leaguename);
				$league->setDivision($division);
				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate);
				//$team->setFlags($dbflag->getFlagsByTeam($team));
				$teams->append($team);
			}			
			$stmt->close();
			$db->disconnect();
			
			return $teams;
		}
	}
	
	public function getTeams() {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('SELECT `team#`, `name`, `league#`, `update`, `away_coolness`, `away_flags`, `home_coolness`, `home_flags`, `and_coolness`, `and_flags`, `or_coolness`, `or_flags`, `plus_coolness`, `plus_flags`, `user#`, `username`, `activationdate` FROM `tteam`')) {
			$stmt->execute();
			$stmt->bind_result($teamID, $name, $leagueID, $update, $away_coolness, $away_flags, $home_coolness, $home_flags, $and_coolness, $and_flags, $or_coolness, $or_flags, $plus_coolness, $plus_flags, $userID, $username, $activationdate);
			
			$teams = new ArrayObject();
			
			while ($stmt->fetch()) {
				$country = new Country();
				$country->setCountryID($countryID);
				
				$division = new Division();
				$division->setDivisionID($divisionID);
				$division->setCountry($country);
				
				$league = new League();
				$league->setLeagueID($leagueID);
				$league->setName($leaguename);
				$league->setDivision($division);
				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate);
				//$team->setFlags($dbflag->getFlagsByTeam($team));
				$teams->append($team);
			}			
			$stmt->close();
			$db->disconnect();
			
			return $teams;
		}
	}
	
	public function getTeamByID($id) {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('SELECT `team#`, `name`, `league#`, `update`, `away_coolness`, `away_flags`, `home_coolness`, `home_flags`, `and_coolness`, `and_flags`, `or_coolness`, `or_flags`, `plus_coolness`, `plus_flags`, `user#`, `username`, `activationdate` FROM `tteam` WHERE `team#` = ?')) {
			$stmt->bind_param('i', $id);
			$stmt->execute();
			$stmt->bind_result($teamID, $name, $leagueID, $update, $away_coolness, $away_flags, $home_coolness, $home_flags, $and_coolness, $and_flags, $or_coolness, $or_flags, $plus_coolness, $plus_flags, $userID, $username, $activationdate);
			
			$team = null;
			
			while ($stmt->fetch()) {
				$country = new Country();
				$country->setCountryID($countryID);
				
				$division = new Division();
				$division->setDivisionID($divisionID);
				$division->setCountry($country);
				
				$league = new League();
				$league->setLeagueID($leagueID);
				$league->setName($leaguename);
				$league->setDivision($division);
				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate);
				$team->setFlags($dbflag->getFlagsByTeam($team));
			}			
			$stmt->close();
			$db->disconnect();
			
			return $team;
		}
	}
	
	public function getTeamByUserID($userID) {
		$db = new DB();
		$dbflag = new DBFlag();
		
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('SELECT `team#`, `name`, `league#`, `update`, `away_coolness`, `away_flags`, `home_coolness`, `home_flags`, `and_coolness`, `and_flags`, `or_coolness`, `or_flags`, `plus_coolness`, `plus_flags`, `user#`, `username`, `activationdate` FROM `tteam` WHERE `user#` = ?')) {
			$stmt->bind_param('i', $userID);
			$stmt->execute();
			$stmt->bind_result($teamID, $name, $leagueID, $update, $away_coolness, $away_flags, $home_coolness, $home_flags, $and_coolness, $and_flags, $or_coolness, $or_flags, $plus_coolness, $plus_flags, $userID, $username, $activationdate);
			
			$team = null;
			
			while ($stmt->fetch()) {
				$country = new Country();
				$country->setCountryID($countryID);
				
				$division = new Division();
				$division->setDivisionID($divisionID);
				$division->setCountry($country);
				
				$league = new League();
				$league->setLeagueID($leagueID);
				$league->setName($leaguename);
				$league->setDivision($division);
				
				$team = new Team($teamID, $name, $league, strtotime($update), $away_coolness, $away_flags, null, $home_coolness, $home_flags, null, $and_coolness, $and_flags, null, $or_coolness, $or_flags, null, $plus_coolness, $plus_flags, null, $userID, $username, $activationdate);
				//$team->setFlags($dbflag->getFlagsByTeam($team));
			}			
			$stmt->close();
			$db->disconnect();
			
			return $team;
		}
	}
	
	public function saveTeams($teams) {
		$db = new DB();
		$dbflag = new DBFlag();
		$dballiancemember = new DBAllianceMember();
		
		$connection = $db->connect();
		
		if ($stmt = $connection->prepare('INSERT INTO `tteam` (`team#`, `name`, `league#`, `update`, `away_coolness`, `away_flags`, `home_coolness`, `home_flags`, `and_coolness`, `and_flags`, `or_coolness`, `or_flags`, `plus_coolness`, `plus_flags`, `user#`, `username`, `activationdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `league#`=VALUES(`league#`), `name`=VALUES(`name`), `update`=VALUES(`update`), `away_coolness`=VALUES(`away_coolness`), `away_flags`=VALUES(`away_flags`), `home_coolness`=VALUES(`home_coolness`), `home_flags`=VALUES(`home_flags`), `and_coolness`=VALUES(`and_coolness`), `and_flags`=VALUES(`and_flags`), `or_coolness`=VALUES(`or_coolness`), `or_flags`=VALUES(`or_flags`), `plus_coolness`=VALUES(`plus_coolness`), `plus_flags`=VALUES(`plus_flags`), `user#`=VALUES(`user#`), `username`=VALUES(`username`), `activationdate`=VALUES(`activationdate`)')) {
			foreach ($teams as $team) {
				$stmt->bind_param('isisdididididiiss', $teamID, $name, $leagueID, $update, $away_coolness, $away_flags, $home_coolness, $home_flags, $and_coolness, $and_flags, $or_coolness, $or_flags, $plus_coolness, $plus_flags, $userID, $username, $activationdate);
				
				$teamID = $team->getTeamID();
				$name = $team->getName();
				$leagueID = $team->getLeague()->getLeagueID();
				$update = date('Y-m-d H:i:s', $team->getUpdate());
				$away_coolness = $team->getAwayCoolness();
				$away_flags = $team->getAwayFlags();
				$home_coolness = $team->getHomeCoolness();
				$home_flags = $team->getHomeFlags();
				$and_coolness = $team->getAndCoolness();
				$and_flags = $team->getAndFlags();
				$or_coolness = $team->getOrCoolness();
				$or_flags = $team->getOrFlags();
				$plus_coolness = $team->getPlusCoolness();
				$plus_flags = $team->getPlusFlags();
				$userID = $team->getUserID();
				$username = $team->getUsername();
				$activationdate = $team->getActivationDate();
								
				$stmt->execute();
				
				$flags = $team->getFlags();
				if (isset($flags)) {
					$dbflag->saveFlags($team->getFlags());
				}
				
				$alliances = $team->getAlliances();
				if (isset($alliances)) {
					$dballiancemember->saveAllianceMembership($team);
				}
				
			}
			$stmt->close();
			$db->disconnect();
		}
	}
	
	public function saveTeam($team) {
		$db = new DB();
		$dbflag = new DBFlag();
		$dballiancemember = new DBAllianceMember();
		
		$connection = $db->connect();
				
		if ($stmt = $connection->prepare('INSERT INTO `tteam` (`team#`, `name`, `league#`, `update`, `away_coolness`, `away_flags`, `home_coolness`, `home_flags`, `and_coolness`, `and_flags`, `or_coolness`, `or_flags`, `plus_coolness`, `plus_flags`, `user#`, `username`, `activationdate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `league#`=VALUES(`league#`), `name`=VALUES(`name`), `update`=VALUES(`update`), `away_coolness`=VALUES(`away_coolness`), `away_flags`=VALUES(`away_flags`), `home_coolness`=VALUES(`home_coolness`), `home_flags`=VALUES(`home_flags`), `and_coolness`=VALUES(`and_coolness`), `and_flags`=VALUES(`and_flags`), `or_coolness`=VALUES(`or_coolness`), `or_flags`=VALUES(`or_flags`), `plus_coolness`=VALUES(`plus_coolness`), `plus_flags`=VALUES(`plus_flags`), `user#`=VALUES(`user#`), `username`=VALUES(`username`), `activationdate`=VALUES(`activationdate`)')) {
			$stmt->bind_param('isisdididididiiss', $teamID, $name, $leagueID, $update, $away_coolness, $away_flags, $home_coolness, $home_flags, $and_coolness, $and_flags, $or_coolness, $or_flags, $plus_coolness, $plus_flags, $userID, $username, $activationdate);
			
			$teamID = $team->getTeamID();
			$name = $team->getName();
			$leagueID = $team->getLeague()->getLeagueID();
			$update = date('Y-m-d H:i:s', $team->getUpdate());
			$away_coolness = $team->getAwayCoolness();
			$away_flags = $team->getAwayFlags();
			$home_coolness = $team->getHomeCoolness();
			$home_flags = $team->getHomeFlags();
			$and_coolness = $team->getAndCoolness();
			$and_flags = $team->getAndFlags();
			$or_coolness = $team->getOrCoolness();
			$or_flags = $team->getOrFlags();
			$plus_coolness = $team->getPlusCoolness();
			$plus_flags = $team->getPlusFlags();
			$userID = $team->getUserID();
			$username = $team->getUsername();
			$activationdate = $team->getActivationDate();
			
			$stmt->execute();
			
			$flags = $team->getFlags();
			if (isset($flags)) {
				$dbflag->saveFlags($team);
			}
			
			$alliances = $team->getAlliances();
			if (isset($alliances)) {
				$dballiancemember->saveAllianceMembership($team);
			}
			
			$stmt->close();
			$db->disconnect();
		}
	}
	
	public function deleteTeamByID($id) {
		$db = new DB();
		
		$connection = $db->connect();
				
		if ($stmt = $connection->prepare('DELETE FROM `tteam` WHERE `team#` = ?')) {
			$stmt->bind_param('i', $teamID);
			
			$teamID = $id;
			
			$stmt->execute();
			
			$stmt->close();
			$db->disconnect();
		}
	}
	
	private function createSelect($countryID, $divisionID, $leagueID, $allianceID, $fromdate, $todate, $select, $order = null) {
		
		$from = " FROM `tteam` t, `tleague` l";
		$where = " WHERE t.`league#` = l.`league#`";
		
		$type = "";
		$param = array();
		if ($leagueID != null) {
			$where .= " AND l.`league#`= ?";
			$type .= "i";
			$param[] = $leagueID;
		}
		else if ($countryID != null && $divisionID != null) {
			$where .= " AND l.`country#` = ? AND l.`division#` = ?";
			$type .= "i";
			$param[] = $countryID;
			$type .= "i";
			$param[] = $divisionID;
		}
		else if ($countryID != null) {
			$where .= " AND l.`country#` = ?";
			$type .= "i";
			$param[] = $countryID;
		}
		else if ($divisionID != null) {
			$where .= " AND l.`division#` = ?";
			$type .= "i";
			$param[] = $divisionID;
		}
		
		if ($allianceID != null) {
			$from .= ", `talliancemember` am";
			$where .= " AND t.`team#` = am.`team#` AND am.`alliance#` = ?";
			$type .= "i";
			$param[] = $allianceID;
		}
		
		if ($fromdate != null) {
			$where .= " AND t.`activationdate` >= ?";
			$type .= 's';
			$param[] = date("Y-m-d", $fromdate);			
		}
		
		if ($todate != null) {
			$where .= " AND t.`activationdate` < ?";
			$type .= 's';
			$param[] = date("Y-m-d", $todate);			
		}
		
		$this->type = $type;
		$this->params = $param;
		
		return $select . $from . $where . $order;
	}
	
}

?>